package com.bss.dao;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.bss.beans.Patient;
import com.bss.command.PatientCommand;
import com.bss.framework.FrontController;

public class PatientRecordDao {

	public static int AddNewPatient(Patient patient) throws Exception {
		Connection conn;
		PreparedStatement Ps;
		String insertString = "insert into patient_record(patientId,patientFirstName,patientLastName,patientMiddleName,address,contactNo,idCardType,sex,idCardNumber,profession,medicalProblem,tatacaseno) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		int patientId = 0;
		conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(insertString);
			patientId = FindMaxPatientId() + 1;
			Ps.setInt(1, patientId);
			Ps.setString(2, patient.getSPatientFirstName());
			Ps.setString(3, patient.getSPatientLastName());
			Ps.setString(4, patient.getSPatientMiddleName());
			Ps.setString(5, patient.getSAddress());
			Ps.setString(6, patient.getSContactNo());
			Ps.setString(7, patient.getSIdCardType());
			Ps.setString(8, patient.getSSex());
			Ps.setString(9, patient.getSIdCardNo());
			Ps.setString(10, patient.getSProfession());
			Ps.setString(11, patient.getSMedicalProblem());
			Ps.setString(12, patient.getSTataCaseNo());

			Ps.executeUpdate();

		} finally {
			ConnectionDao.closeConnection(conn);

		}

		return patientId;
	}

	public static int FindMaxPatientId() throws Exception {
		Connection conn;
		ResultSet Rs;
		PreparedStatement Ps;
		int patientId = 0;

		String insertString = "select max(patientId) from patient_record";

		conn = ConnectionDao.getConnection1();

		try {
			Ps = conn.prepareStatement(insertString);
			Rs = Ps.executeQuery();

			while (Rs.next()) {
				patientId = Rs.getInt(1);
			}

			return patientId;
		} finally {
			ConnectionDao.closeConnection(conn);
		}
	}

	public static ArrayList<String> loadFirstNames() throws Exception {
		ResultSet Rs = null;
		String user = null;
		ArrayList<String> userlist = new ArrayList<String>();
		String selectQuerry = "select patientFirstName from patient_record";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getString(1);
				userlist.add(user);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return userlist;
	}

	public static ArrayList<String> loadMiddleNames() throws Exception {
		ResultSet Rs = null;
		String user = null;
		ArrayList<String> userlist = new ArrayList<String>();
		String selectQuerry = "select patientMiddleName,patientId from patient_record";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getString(1);
				userlist.add(user);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return userlist;
	}

	public static ArrayList<String> loadLastNames() throws Exception {
		ResultSet Rs = null;
		String user = null;
		ArrayList<String> userlist = new ArrayList<String>();
		String selectQuerry = "select patientLastName,patientId from patient_record";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getString(1);
				userlist.add(user);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return userlist;
	}

	public static ArrayList<Integer> loadID() throws Exception {
		ResultSet Rs = null;
		Integer user = null;
		ArrayList<Integer> userlist = new ArrayList<Integer>();
		String selectQuerry = "select patientId from patient_record";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getInt(1);
				userlist.add(user);
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return userlist;
	}

	public static Patient LoadPatient(int Id) throws Exception {
		Patient patient = new Patient();

		ResultSet Rs = null;

		String selectQuerry = "select patientFirstName,patientLastName,patientMiddleName,address,contactNo,idCardType,sex,idCardNumber,profession,medicalProblem,tatacaseno from patient_record where patientId=?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);

			Ps.setInt(1, Id);

			Rs = Ps.executeQuery();

			while (Rs.next()) {

				patient.setSPatientFirstName(Rs.getString(1));
				patient.setSPatientLastName(Rs.getString(2));
				patient.setSPatientMiddleName(Rs.getString(3));
				patient.setSAddress(Rs.getString(4));
				patient.setSContactNo(Rs.getString(5));
				patient.setSIdCardType(Rs.getString(6));
				patient.setSSex(Rs.getString(7));
				patient.setSIdCardNo(Rs.getString(8));
				patient.setSProfession(Rs.getString(9));
				patient.setSMedicalProblem(Rs.getString(10));
				patient.setSTataCaseNo(Rs.getString(11));

				break;
			}

		} finally {
			ConnectionDao.closeConnection(conn);
		}

		return patient;
	}

	public static boolean updatePatient(Patient patient) throws Exception {
		Connection conn;
		PreparedStatement Ps;

		conn = ConnectionDao.getConnection1();

		try {

			String strSelect = "update patient_record set patientFirstName=?,patientLastName=?,patientMiddleName=?,address=?,contactNo=?,idCardType=?,sex=?,idCardNumber=?,profession=?,medicalProblem=?,tatacaseno=? where patientId=?";

			Ps = conn.prepareStatement(strSelect);

			Ps.setInt(12, patient.getIPatientId());
			Ps.setString(1, patient.getSPatientFirstName());
			Ps.setString(2, patient.getSPatientLastName());
			Ps.setString(3, patient.getSPatientMiddleName());
			Ps.setString(4, patient.getSAddress());
			Ps.setString(5, patient.getSContactNo());
			Ps.setString(6, patient.getSIdCardType());
			Ps.setString(7, patient.getSSex());
			Ps.setString(8, patient.getSIdCardNo());
			Ps.setString(9, patient.getSProfession());
			Ps.setString(10, patient.getSMedicalProblem());
			Ps.setString(11, patient.getSTataCaseNo());

			Ps.executeUpdate();

		} finally {
			// ConnectionDao.closeConnection(conn);
		}

		return true;
	}

	public static boolean updateLoadImage(int patientId) throws Exception {

		try {

			if (PatientCommand.getImage() == null
					|| PatientCommand.getImage().length == 0 || ! PatientCommand.isClicked()) {
				return false;
			}
			Connection conn;
			PreparedStatement Ps;

			conn = ConnectionDao.getConnection1();

			try {

				String strSelect = "update ignore patient_record set photograph=? where patientId=?";

				Ps = conn.prepareStatement(strSelect);

				InputStream iis = new ByteArrayInputStream(PatientCommand.getImage());							
				
				Ps.setBinaryStream(1, iis, PatientCommand.getImage().length);
				Ps.setInt(2, patientId);
				Ps.executeUpdate();

			} finally {
				// ConnectionDao.closeConnection(conn);
			}

			PatientCommand.setImage(null);
			PatientCommand.setClicked(false);

		} catch (Exception e) {
			FrontController.error("Error uploading image: "
					+ e.fillInStackTrace());
			e.printStackTrace();
		}

		return true;
	}

	public static InputStream downloadImage(int patientId) throws Exception {
		String selectQuerry = "select photograph from patient_record where patientId=?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {
			InputStream sImage;
			ResultSet Rs = null;
			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);

			Rs = Ps.executeQuery();

			while (Rs.next()) {
				sImage = Rs.getBinaryStream(1);
				return sImage;
			}

		} finally {
			// ConnectionDao.closeConnection(conn);
		}
		return null;
	}

	public static String getPatientName(int patientId) throws Exception {
		ResultSet Rs = null;
		String user = null;

		String selectQuerry = "select patientFirstName,patientLastName,patientMiddleName from patient_record where patientId=?";
		PreparedStatement Ps;
		Connection conn = ConnectionDao.getConnection1();

		try {

			Ps = conn.prepareStatement(selectQuerry);
			Ps.setInt(1, patientId);
			Rs = Ps.executeQuery();

			while (Rs.next()) {

				user = Rs.getString(1) + " " + Rs.getString(3) + " "
						+ Rs.getString(2);

			}

		} finally {
			// ConnectionDao.closeConnection(conn);
		}

		return user;
	}

}
